using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
using ROAD_ULITIES;

namespace DAO
{
    public class XeDAO
    {
        #region Inserting
        public static bool InsertXe(XeDTO xe)
        {
            bool result = false;
            try
            {
                //Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@NgayTiepNhan", xe.NgayTiepNhan));
                sqlParams.Add(new SqlParameter("@MaNhanVien", xe.MaNhanVien));
                sqlParams.Add(new SqlParameter("@NgayDangKiem", xe.NgayDangKiem));
                sqlParams.Add(new SqlParameter("@BienSo", xe.BienSo));
                sqlParams.Add(new SqlParameter("@MaLoaiHang", xe.MaLoaiHang));
                sqlParams.Add(new SqlParameter("@MaTrongTai", xe.MaTrongTai));
                sqlParams.Add(new SqlParameter("@MaHangXe", xe.MaHangXe));
                sqlParams.Add(new SqlParameter("@SoKhung", xe.SoKhung));
                sqlParams.Add(new SqlParameter("@NamSanXuat", xe.NamSanXuat));
                sqlParams.Add(new SqlParameter("@HieuXe", xe.HieuXe));
                sqlParams.Add(new SqlParameter("@SoMay", xe.SoMay));
                sqlParams.Add(new SqlParameter("@DungTichBinh", xe.DungTichBinh));

                //Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spInsertXe", sqlParams);
                if (n == 1)
                    return true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result; 
        }
        #endregion

        #region Update
        public static bool UpdateXe(XeDTO xe)
        {
            bool result = true;
            try
            {
                //Create List Sql Parameter
                List<SqlParameter> SqlParams = new List<SqlParameter>();
                SqlParams.Add(new SqlParameter("@NgayTiepNhan", xe.NgayTiepNhan));
                SqlParams.Add(new SqlParameter("@MaNhanVien", xe.MaNhanVien));
                SqlParams.Add(new SqlParameter("@NgayDangKiem", xe.NgayDangKiem));
                SqlParams.Add(new SqlParameter("@BienSo", xe.BienSo));
                SqlParams.Add(new SqlParameter("@MaLoaiHang", xe.MaLoaiHang));
                SqlParams.Add(new SqlParameter("@MaTrongTai", xe.MaTrongTai));
                SqlParams.Add(new SqlParameter("@HieuXe", xe.HieuXe));
                SqlParams.Add(new SqlParameter("@SoKhung", xe.SoKhung));
                SqlParams.Add(new SqlParameter("@NamSanXuat", xe.NamSanXuat));
                SqlParams.Add(new SqlParameter("@MaHangXe", xe.MaHangXe));
                SqlParams.Add(new SqlParameter("@SoMay", xe.SoMay));
                SqlParams.Add(new SqlParameter("@DungTichBinh", xe.DungTichBinh));

                //Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spUpdateXe", SqlParams);
                if (n == 1)
                    return true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region Delete
        public static bool DeleteXeByMaXe(int MaXe)
        {
            bool result = false;
            try
            {
                //Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaXe", MaXe));
                //Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spDeleteXeByMaXe", sqlParams);
                if (n == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion

        #region Retrieving
        public static DataTable TimTinhTrangGiaoHangTheoThang(int Thang)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@thang", Thang));
            DataTable dt = SqlDataAccessHelper.ExecuteQuery("spTinhTrangGiaoHangTheoThang", sqlParams);
            return dt;
        }
        public static void TimNhienLieuTheoThang(int thang, List<String> BienSo,List<int> QuangDuong, List<int> DinhMuc)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@thang", thang));
            DataTable dt = SqlDataAccessHelper.ExecuteQuery("spTimNhienLieuTheoThang", sqlParams);
            foreach (DataRow dr in dt.Rows)
            {
                BienSo.Add(dr["BienSo"].ToString());
                DinhMuc.Add(int.Parse(dr["DinhMuc"].ToString()));
                QuangDuong.Add(Road.LayQuangDuong(int.Parse(dr["ToaDo"].ToString())));
            }
            for (int i = 0; i < BienSo.Count-1; i++)
            {
                for (int j = 0; j < BienSo.Count; j++)
                {
                    if (BienSo[i] == BienSo[j])
                    {
                        BienSo.RemoveAt(j);
                        DinhMuc.RemoveAt(j);
                        QuangDuong[i] = QuangDuong[i] + QuangDuong[j];
                        QuangDuong.RemoveAt(j);
                    }
                }
            }
        }
        public static void TimQuangDuongTheoThang(String BienSo, int Thang, List<String> TenDuong, List<int> Sokm,List<int> ThoiGianDung)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@bienso",BienSo));
            sqlParams.Add(new SqlParameter("@thang",Thang));
            DataTable dt=SqlDataAccessHelper.ExecuteQuery("spTimQuangDuongTheoThang",sqlParams);
             foreach(DataRow dr in dt.Rows)
            {
                //TenDuong.Add(new string());
                //Sokm.Add(new int());
                Road.LayTenDuongQuangDuong(int.Parse(dr["ToaDo"].ToString()), TenDuong, Sokm);
            }
        }
        public static List<XeDTO> SelectXeByLoaiHang(int loaihang)
        {
            List<XeDTO> list = new List<XeDTO>();
            try
            {
                //Create list SqlParameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@LoaiHang", loaihang));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectXeByLoaiHang", sqlParams);
                foreach(DataRow dr in dt.Rows)
                {
                    XeDTO xe = new XeDTO();
                    xe.MaXe=int.Parse(dr["MaXe"].ToString());
                    xe.BienSo = dr["BienSo"].ToString();
                    xe.MaHangXe = int.Parse(dr["MaHangXe"].ToString());
                    xe.MaLoaiHang = int.Parse(dr["MaLoaiHang"].ToString());
                    xe.MaTrongTai = int.Parse(dr["MaTrongTai"].ToString());
                    xe.SoKhung = dr["SoKhung"].ToString();
                    xe.SoMay = int.Parse(dr["SoMay"].ToString());
                    xe.NgayDangKiem = DateTime.Parse(dr["NgayDangKiem"].ToString());
                    xe.NamSanXuat = int.Parse(dr["NamSanXuat"].ToString());
                    xe.HieuXe = dr["HieuXe"].ToString();
                    xe.DungTichBinh = int.Parse(dr["DungTichBinh"].ToString());
                    list.Add(xe);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<XeDTO> SelectXeAll()
        {
            List<XeDTO> list = new List<XeDTO>();
            try
            {
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectXeAll");
                foreach(DataRow dr in dt.Rows)
                {
                    XeDTO xe = new XeDTO();
                    xe.BienSo = dr["BienSo"].ToString();
                    xe.MaHangXe = int.Parse(dr["MaHangXe"].ToString());
                    xe.MaLoaiHang = int.Parse(dr["MaLoaiHang"].ToString());
                    xe.MaTrongTai = int.Parse(dr["MaTrongTai"].ToString());
                    xe.SoKhung = dr["SoKhung"].ToString();
                    xe.SoMay = int.Parse(dr["SoMay"].ToString());
                    xe.NgayDangKiem = DateTime.Parse(dr["NgayDangKiem"].ToString());
                    xe.NamSanXuat = int.Parse(dr["NamSanXuat"].ToString());
                    xe.HieuXe = dr["HieuXe"].ToString();
                    xe.DungTichBinh = int.Parse(dr["DungTichBinh"].ToString());
                    list.Add(xe);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<XeDTO> SelectXeByAll(int maloaihang, int mahangxe, int matrongtai)
        {
            List<XeDTO> list = new List<XeDTO>();
            SqlConnection connect = SqlDataAccessHelper.ConnectSQL();
            SqlCommand command = connect.CreateCommand();
            command.CommandText = "SELCT * FROM XE WHERE XE.MaLoaiHang==maloaihang,XE.MaHangXe==mahangxe,XE.MaTrongTai==matrongtai";
            SqlDataReader dr = command.ExecuteReader();
            while(dr.Read())
            {
                XeDTO xe = new XeDTO();
                xe.MaXe = (int)dr["MaXe"];
                xe.MaTrongTai = (int)dr["MaTrongTai"];
                xe.MaHangXe = (int)dr["MaHangXe"];
                xe.NamSanXuat = (int)dr["NamSanXuat"];
                xe.NgayDangKiem = (DateTime)dr["NgayDangKiem"];
                xe.NgayTiepNhan = (DateTime)dr["NgayTiepNhan"];
                xe.SoKhung = (string)dr["SoKhung"];
                xe.SoMay = (int)dr["SoMay"];
                xe.HieuXe = (string)dr["HieuXe"];
                xe.MaLoaiHang = (int)dr["MaHangXe"];
                xe.BienSo = (string)dr["BienSo"];
                xe.DungTichBinh = (int)dr["DungTichBinh"];
                list.Add(xe);
            }
            dr.Close();
            connect.Close();

            return list;
        }

        public static XeDTO SelectXeByMaXe(int MaXe)
        {
            XeDTO xe = new XeDTO();
            try
            {
                //Create List Sql Paramater
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaXe", MaXe));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectXeByMaXe", sqlParams);
                DataRow dr = dt.Rows[0];
                xe.MaXe = int.Parse(dr["MaXe"].ToString());
                xe.BienSo = dr["BienSo"].ToString();
                xe.MaHangXe = int.Parse(dr["MaHangXe"].ToString());
                xe.MaLoaiHang = int.Parse(dr["MaLoaiHang"].ToString());
                xe.MaTrongTai = int.Parse(dr["MaTrongTai"].ToString());
                xe.SoKhung = dr["SoKhung"].ToString();
                xe.SoMay = int.Parse(dr["SoMay"].ToString());
                xe.NgayDangKiem = DateTime.Parse(dr["NgayDangKiem"].ToString());
                xe.NamSanXuat = int.Parse(dr["NamSanXuat"].ToString());
                xe.HieuXe = dr["HieuXe"].ToString();
                xe.DungTichBinh = int.Parse(dr["DungTichBinh"].ToString());
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return xe;
        }

        public static bool CheckXeByMaXe(int MaXe)
        {
            bool result = false;
            try
            {
                //Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaXe", MaXe));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectXeByMaXe");
                if(dt.Rows.Count == 1)
                {
                    result = true;
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        
        #endregion

    }
}
